﻿CREATE PROCEDURE [dbo].[ASMS_SP_QueryStaffPagedList]
	@filter nvarchar(64),
	@businessId uniqueidentifier,
	@stationId uniqueidentifier,
	@status int,
	@start int, 
	@limit int,
	@total int output
AS
if(@filter is null)
begin
	SELECT T.* 
	FROM (SELECT ROW_NUMBER() over (order by S.Name desc) as _RowIndex, S.*
		from ASMS_Staff as S
		where S.BusinessId = @businessId
			and S.Status = @status
			and S.Id in (select distinct StaffId from ASMS_StaffPosition where StationId=@stationId)) as T
	WHERE _RowIndex > @start and _RowIndex <= @start + @limit

	set @total = (SELECT count(S.Id)
		from ASMS_Staff as S
		where S.BusinessId = @businessId
			and S.Status = @status
			and S.Id in (select distinct StaffId from ASMS_StaffPosition where StationId=@stationId))
end
else
begin
	SELECT T.* 
	FROM (SELECT ROW_NUMBER() over (order by S.Name desc) as _RowIndex, S.*
		from ASMS_Staff as S
		where S.BusinessId = @businessId
			and S.Name like '%'+@filter+'%'
			and S.Status = @status
			and S.Id in (select distinct StaffId from ASMS_StaffPosition where StationId=@stationId)) as T
	WHERE _RowIndex > @start and _RowIndex <= @start + @limit
	
	set @total = (SELECT count(S.Id)
		from ASMS_Staff as S
		where S.BusinessId = @businessId
			and S.Name like '%'+@filter+'%'
			and S.Status = @status
			and S.Id in (select distinct StaffId from ASMS_StaffPosition where StationId=@stationId))
end
RETURN 0
